from database.db_handler import MysqlHander
from database.db_business import DbBusiness
import re
import json

relation_list={"1001":"十大持仓",
               "1002":"十大持仓",
               "1003":"作为十大股东持有的股票",
               "1004":"十大股东",
               "2001":"指数",
               "2002":"产品",
               "2003":"特色",
               "2004":"概念",
               "2005":"热点"}

class PageData:
    def __init__(self):
        self.db = MysqlHander("config.ini")
        self.bu = DbBusiness()
        self.file = open("./out/page_data.sql", "w", encoding="utf8")
    
    def query_name_by_code(self, code, data_type):
        print("data_type:" + str(data_type))
        tb = ""
        if 0 == data_type:
            tb = "entity_gupiao"
        elif 1 == data_type:
            tb = "entity_jijin"
        elif 2 == data_type:
            tb = "entity_gudong"
        sql = "select Fname from " + tb + " where Fcode='" + code + "';"
        print(sql)
        names = self.db.query_sql(sql)
        if len(names) == 1:
            return names[0][0]
        return ""

    def query_gupiao_data(self, sql, data_type):
        #sql = "select Fcode,Fname from entity_gupiao;"
        stock_name = self.bu.query_stock_code_name()
        print(sql)
        self.gupiao_data = self.db.query_sql(sql)
        for gupiao in self.gupiao_data:
            gupiao_page_data = {}
            gupiao_page_data["code"] = gupiao[0]
            print(gupiao[1])
            gupiao_page_data["name"] = gupiao[1]
            gupiao_page_data["type"] = data_type
            gupiao_page_data["nodes"] = []
            current_relation = ""
            relation1_sql = "select Frelation_id from relation_data_01 where Fsrc_code='" + gupiao[0] + "' and Fsrc_type=" + str(data_type) + " and Fvalid = 0 group by Frelation_id;"
            print(relation1_sql)
            relation1s = self.db.query_sql(relation1_sql)
            for relation in relation1s:
                attrs_sql = "select Fdst_code,Fdst_type from relation_data_01 where Fsrc_code='" + gupiao[0] + "' and Frelation_id=" + str(relation[0]) + " and Fvalid = 0 order by Forder_tag;"
                print(attrs_sql)
                attrs = self.db.query_sql(attrs_sql)
                relation_type = {}
                relation_type["name"] = relation_list[str(relation[0])]
                relation_type["nodes"] = []
                for attr in attrs:
                    attr_node = {}
                    attr_node["name"] = self.query_name_by_code(attr[0], attr[1])
                    attr_node["code"] = attr[0]
                    attr_node["nodes"] = []
                    limit = 5
                    if 1001 == relation[0]:
                        limit = 10
                    #same_data_sql = "select Fsrc_code,Fsrc_type from relation_data_01 where Fdst_code='" + attr[0] + "' and Frelation_id=" + str(relation[0]) + " and Fdst_type=" + str(attr[1]) + " and Fsrc_code != '" + gupiao[0] + "' limit 20;"
                    same_data_sql = "select Fsrc_code,Fsrc_type from relation_data_01 where Fdst_code='" + attr[0] + "' and Frelation_id=" + str(relation[0]) + " and Fdst_type=" + str(attr[1]) + " order by Forder_tag limit " + str(limit) + ";"
                    print(same_data_sql)
                    same_datas = self.db.query_sql(same_data_sql)
                    #sames = []
                    for same_data_val in same_datas:
                        same = {}
                        same["code"] = same_data_val[0]
                        if same_data_val[0] in stock_name.keys():
                            same["name"] = stock_name[same_data_val[0]] #self.query_name_by_code(same_data_val[0], same_data_val[1])
                        else:
                            same["name"] = ""
                        #same["name"] = self.query_name_by_code(same_data_val[0], same_data_val[1])
                        #sames.append(same)
                        attr_node["nodes"].append(same)
                    relation_type["nodes"].append(attr_node)
                #gupiao_page_data["nodes"].append(relation_type)    
                self.write_file_data(json.dumps(relation_type, ensure_ascii=False), gupiao[0], gupiao[1], data_type, relation[0])    
    
    def query_gupiao_data_r2(self, sql, data_type):
        #sql = "select Fcode,Fname from entity_gupiao;"
        print(sql)
        self.gupiao_data = self.db.query_sql(sql)
        for gupiao in self.gupiao_data:
            gupiao_page_data = {}
            gupiao_page_data["code"] = gupiao[0]
            print(gupiao[1])
            gupiao_page_data["name"] = gupiao[1]
            gupiao_page_data["type"] = data_type
            gupiao_page_data["nodes"] = []
            current_relation = ""
            relation1_sql = "select Frelation_id from relation_data_02 where Fsrc_code='" + gupiao[0] + "' and Fsrc_type=" + str(data_type) + " and Fvalid = 0 group by Frelation_id;"
            print(relation1_sql)
            relation1s = self.db.query_sql(relation1_sql)
            for relation in relation1s:
                attrs_sql = "select Fattribute_name from relation_data_02 where Fsrc_code='" + gupiao[0] + "' and Frelation_id=" + str(relation[0]) + " and Fvalid = 0 order by Forder_tag;"
                print(attrs_sql)
                attrs = self.db.query_sql(attrs_sql)
                relation_type = {}
                relation_type["name"] = relation_list[str(relation[0])]
                relation_type["nodes"] = []
                for attr in attrs:
                    attr_node = {}
                    attr_node["name"] = attr[0] #self.query_name_by_code(attr[0], attr[1])
                    #attr_node["code"] = attr[0]
                    attr_node["nodes"] = []
                    limit = 5
                    if 1001 == relation[0]:
                        limit = 10
                    #same_data_sql = "select Fsrc_code,Fsrc_type from relation_data_01 where Fdst_code='" + attr[0] + "' and Frelation_id=" + str(relation[0]) + " and Fdst_type=" + str(attr[1]) + " and Fsrc_code != '" + gupiao[0] + "' limit 20;"
                    same_data_sql = "select Fsrc_code,Fsrc_type from relation_data_02 where Fattribute_name='" + attr[0] + "' and Frelation_id=" + str(relation[0]) + " order by Forder_tag limit " + str(limit) + ";"
                    print(same_data_sql)
                    same_datas = self.db.query_sql(same_data_sql)
                    #sames = []
                    for same_data_val in same_datas:
                        same = {}
                        same["code"] = same_data_val[0]
                        same["name"] = self.query_name_by_code(same_data_val[0], same_data_val[1])
                        #sames.append(same)
                        attr_node["nodes"].append(same)
                    relation_type["nodes"].append(attr_node)
                #gupiao_page_data["nodes"].append(relation_type)    
                self.write_file_data(json.dumps(relation_type, ensure_ascii=False), gupiao[0], gupiao[1], data_type, relation[0])    
    

    def query_relation(self):
        stock_name = self.bu.query_stock_code_name()
        relation1_sql = "select Frelation_id from relation_data_02 where Fvalid = 0 group by Frelation_id;"
        print(relation1_sql)
        relation1s = self.db.query_sql(relation1_sql)
        for relation in relation1s:
            attrs_sql = "select Fattribute_name from relation_data_02 where Frelation_id=" + str(relation[0]) + " and Fvalid = 0 order by Forder_tag;"
            print(attrs_sql)
            attrs = self.db.query_sql(attrs_sql)
            for attr in attrs:
                attr_node = {}
                attr_node["relation_name"] = relation_list[str(relation[0])]
                attr_node["attr_name"] = attr[0] #self.query_name_by_code(attr[0], attr[1])
                attr_node["nodes"] = []
                same_data_sql = "select Fsrc_code,Fsrc_type from relation_data_02 where Fattribute_name='" + attr[0] + "' and Frelation_id=" + str(relation[0]) + " order by Forder_tag;"
                print(same_data_sql)
                same_datas = self.db.query_sql(same_data_sql)
                #sames = []
                for same_data_val in same_datas:
                    same = {}
                    same["code"] = same_data_val[0]
                    if same_data_val[0] in stock_name.keys():
                        same["name"] = stock_name[same_data_val[0]] #self.query_name_by_code(same_data_val[0], same_data_val[1])
                    else:
                        same["name"] = ""
                    #sames.append(same)
                    attr_node["nodes"].append(same)   
                self.write_file_attr_data(json.dumps(attr_node, ensure_ascii=False), relation[0], attr[0]) 


    def query_relation_v1(self):
        stock_name = self.bu.query_stock_code_name()
        gudong_name = self.bu.query_gudong_code_name()
        relation1_sql = "select Frelation_id from relation_data_01 where Frelation_id=1003 and Fvalid = 0 group by Frelation_id;"
        print(relation1_sql)
        relation1s = self.db.query_sql(relation1_sql)
        for relation in relation1s:
            attrs_sql = "select Fdst_code,Fdst_type from relation_data_01 where Frelation_id=" + str(relation[0]) + " and Fvalid = 0 group by Fdst_code,Fdst_type;"
            print(attrs_sql)
            attrs = self.db.query_sql(attrs_sql)
            for attr in attrs:
                attr_node = {}
                attr_node["relation_name"] = relation_list[str(relation[0])]
                attr_node["attr_name"] = "[" + gudong_name[attr[0]] + "]" + attr_node["relation_name"]
                attr_node["nodes"] = []
                same_data_sql = "select Fsrc_code,Fsrc_type from relation_data_01 where Fdst_code='" + attr[0] + "' and Fdst_type=" + str(attr[1]) + " and Frelation_id=" + str(relation[0]) + " order by Forder_tag;"
                print(same_data_sql)
                same_datas = self.db.query_sql(same_data_sql)
                #sames = []
                for same_data_val in same_datas:
                    same = {}
                    same["code"] = same_data_val[0]
                    if same_data_val[0] in stock_name.keys():
                        same["name"] = stock_name[same_data_val[0]] #self.query_name_by_code(same_data_val[0], same_data_val[1])
                    else:
                        same["name"] = ""
                    #sames.append(same)
                    attr_node["nodes"].append(same)   
                self.write_file_attr_data(json.dumps(attr_node, ensure_ascii=False), relation[0], attr[0])    


    def process_name(self, name):
        self.need_split = 0
        new_name = name.replace("&", "").replace("(", "").replace(")", "").replace(".", "").replace("-", "")
        #简单用空格判断,碰到复杂的再调整
        if new_name.find(" ") > 1:
            self.need_split = 1
            return new_name.lower()
        return new_name
    
    def query_gudong_data(self, sql, data_type):
        #sql = "select Fcode,Fname from entity_gupiao;"
        print(sql)
        gudong_data = self.db.query_sql(sql)
        for gudong in gudong_data:
            new_name = self.process_name(gudong[1])
            if self.need_split == 1:
                names = new_name.split(" ")
                for name in names:
                    self.add_data(name, gudong[0], gudong[1], data_type)
            else:
                for i in range (0,len(new_name)):
                    for j in range (i + 1,len(new_name) + 1):
                        #print(gudong[1][i:j])
                        self.add_data(new_name[i:j], gudong[0], gudong[1], data_type)
    
    def write_file_attr_data(self, json_data, relation_id, attr_name):
        try:
            insert_sql = "insert into attribute_data(Fattribute_name,Frelation_id,Fmatch_text,Fvalid,Fcreate_time,Fmodify_time) values ('" + attr_name + "'," + str(relation_id) + ",'" + json_data + "',0,now(),now());\n"
            self.db.execute_not_safe(insert_sql)
            print(insert_sql)
        except Exception as e:
            update_sql = "update attribute_data set Fmodify_time = now(), Fmatch_text='" + json_data + "' where Frelation_id=" + str(relation_id) + " and Fattribute_name='" + attr_name + "';\n"
            self.db.execute_not_safe(update_sql)
            print(update_sql)
        #self.file.write(update_sql)


    def write_file_data(self, json_data, code, name, entity_type, relation_id):
        try:
            sql = "insert into entity_match (Fcode, Fname, Fentity_type, Frealtion_id, Fmatch_text, Fcreate_time, Fmodify_time) values ('" + code + "', '" + name + "', " + str(entity_type) + ", " + str(relation_id) + ",'" + json_data + "', now(), now());\n"
            print(sql)
            #self.file.write(sql)
            self.db.execute_not_safe(sql)
        except Exception as e:
            print(str(e))
            pass
        update_sql = "update entity_match set Fmodify_time = now(), Fname='" + name + "', Fmatch_text='" + json_data + "' where Fcode='" + code + "' and Fentity_type=" + str(entity_type) + " and Frealtion_id=" + str(relation_id) + ";\n"
        self.db.execute_not_safe(update_sql)
        print(update_sql)
        #self.file.write(update_sql)

    def close(self):
        self.file.close()

if __name__ == '__main__':
    code = PageData()
    #code.query_gupiao_data_r2("select Fcode,Fname from entity_gupiao;", 0)
    code.query_gupiao_data("select Fcode,Fname from entity_gupiao;", 0)
    #code.query_relation()
    #code.query_relation_v1()


    #code.query_gupiao_data("select Fcode,Fname from entity_jijin;", 1)
    #十大股东中包含英文机构，需要单独进行分词处理
    #code.query_gudong_data("select Fcode,Fname from entity_gudong;", 2)
    #code.write_file_data()
    code.close()